package elicecn.util;

import java.io.BufferedReader;
import java.io.FileReader;
import java.io.IOException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.Iterator;
import java.util.List;
import java.util.Map;

/**
 * 将字典数据写入数据库
 * 
 * <pre>
 * 项目名称
 * 子项目名称;子项目名称
 * </pre>
 * 
 * @author 龚亮
 * @version 1.0 - 2010-4-30
 */
public class LoadDic {
	public static void main(String[] args) throws ClassNotFoundException,
			SQLException, IOException {
		String file = "D:/dev/[BONC]/116114/dic2.txt";
		String driver = "oracle.jdbc.driver.OracleDriver";
		String url = "jdbc:oracle:thin:@eli:1521:orcl";
		String user = "u116114";
		String password = "oracle";
		Connection cnt = getConnection(driver, url, user, password);
		Map<String, List<String>> resources = loadRecords(file);
		saveResources(resources, cnt);
	}

	public static Connection getConnection(String driver, String url,
			String user, String password) throws ClassNotFoundException,
			SQLException {
		Class.forName(driver);
		return DriverManager.getConnection(url, user, password);
	}

	public static void saveResources(Map<String, List<String>> resources,
			Connection cnt) throws SQLException {
		Iterator<String> it = resources.keySet().iterator();
		String ids = "select seq_dic_id.nextval from dual";
		PreparedStatement stt = null;
		ResultSet rs = null;
		while (it.hasNext()) {
			String key = it.next();
			List<String> values = resources.get(key);
			stt = cnt.prepareStatement(ids);
			rs = stt.executeQuery();
			long id = 0L;
			if (rs.next()) {
				id = rs.getLong(1);
			} else {
				stt.close();
				continue;
			}
			rs.close();
			stt.close();
			stt = cnt.prepareStatement(getInsSql(id, key, 0, 0));
			int rows = stt.executeUpdate();
			stt.close();
			if (rows == 1) {
				int size = values.size();
				for (int i = 0; i < size; i++) {
					String value = values.get(i);
					stt = cnt.prepareStatement(ids);
					rs = stt.executeQuery();
					long sid = 0;
					if (rs.next()) {
						sid = rs.getInt(1);
					} else {
						stt.close();
						continue;
					}
					rs.close();
					stt.close();
					stt = cnt.prepareStatement(getInsSql(sid, value, i, id));
					stt.executeUpdate();
					stt.close();
				}
			} else {
				System.out.println("error: " + id + " - " + key);
			}
		}
		cnt.close();
	}

	public static String getInsSql(long id, String key, long seq, long pid) {
		String ins = "insert into dic(ID, DIC_NAME, SORT_SEQ, PARENT_ID)values(#ID#,#DIC_NAME#,#SORT_SEQ#,#PARENT_ID#)";
		return ins.replace("#ID#", "'" + id + "'").replace("#DIC_NAME#",
				"'" + key + "'").replace("#SORT_SEQ#",
				"'" + System.currentTimeMillis() + "'").replace("#PARENT_ID#",
				"'" + pid + "'");
	}

	public static Map<String, List<String>> loadRecords(String file)
			throws IOException {
		Map<String, List<String>> map = new HashMap<String, List<String>>(0);
		BufferedReader reader = new BufferedReader(new FileReader(file));
		String key, values;
		while ((key = reader.readLine()) != null) {
			values = reader.readLine();
			if (key != null && values != null) {
				String[] valueArray = values.split(";");
				List<String> list = new ArrayList<String>();
				for (String item : valueArray) {
					list.add(item);
				}
				map.put(key, list);
			}
		}
		return map;
	}
}
